begin tran
create table #temp_sku (org_sku nchar(15), new_sku nchar(15))

delete from tmp_sku where isnull(org_sku, '') = '' or isnull(new_sku, '') = ''

insert into #temp_sku(org_sku, new_sku)
select org_sku, new_sku from tmp_sku 

insert into pos_sku_chg_log (chg_date, org_sku, new_sku)
select getdate() chg_date, org_sku, new_sku  from tmp_sku 

declare @upd_id int
select @upd_id = convert(int, var_data) from igs_parm with(updlock) where var_name = 'gv_upd_id'

alter table ms_style disable trigger all
alter table pos_sku disable trigger all
alter table ms_sku disable trigger all
alter table dc_order_sku disable trigger all
alter table dc_pick_constr disable trigger all
alter table dc_alc_ava_qty disable trigger all
alter table dc_alc_dtl disable trigger all
alter table dc_pick_dtl disable trigger all
alter table fg_st_ctrl disable trigger all
alter table fg_stockin_dtl disable trigger all
alter table fg_trx_dtl disable trigger all
alter table fg_trx_constr disable trigger all
alter table fg_stkform_item disable trigger all
alter table fg_stk_item disable trigger all
alter table fg_stk_constr disable trigger all
alter table fg_ship_constr disable trigger all
alter table fg_return_constr disable trigger all
alter table pos_stockin_dtl disable trigger all
alter table pos_stockin_constr disable trigger all
alter table pos_trx_apply_dtl disable trigger all
alter table pos_trx_out_dtl disable trigger all
alter table pos_trx_out_constr disable trigger all
alter table pos_trx_in_dtl disable trigger all
alter table pos_trx_in_constr disable trigger all
alter table pos_sales_dtl disable trigger all
alter table pos_return_dtl disable trigger all
alter table pos_return_constr disable trigger all
alter table pos_stk_item disable trigger all
alter table pos_stkform_dtl disable trigger all
alter table pos_stk_constr disable trigger all
alter table pos_bin_stock disable trigger all
alter table pos_st_ctrl disable trigger all
alter table pos_st_daily_bal disable trigger all


update ms_style set upd_id = @upd_id where exists (select * from #temp_sku t, pos_sku k where t.org_sku = k.sku and k.style_no = ms_style.style_no)

update pos_sku set sku = t.new_sku, upd_id = @upd_id from #temp_sku t where t.org_sku = pos_sku.sku
update ms_sku set sku = t.new_sku from #temp_sku t where t.org_sku = ms_sku.sku

--update ReportServer.dbo.dcPickingDetail set sku = t.new_sku from #temp_sku t where t.org_sku = sku

update dc_order_sku set sku = t.new_sku from #temp_sku t where t.org_sku = dc_order_sku.sku
update dc_pick_constr set sku = t.new_sku from #temp_sku t where t.org_sku = dc_pick_constr.sku
update dc_alc_ava_qty set sku = t.new_sku from #temp_sku t where t.org_sku = dc_alc_ava_qty.sku
update dc_alc_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = dc_alc_dtl.sku
update dc_pick_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = dc_pick_dtl.sku
update fg_st_ctrl set sku = t.new_sku from #temp_sku t where t.org_sku = fg_st_ctrl.sku
update fg_stockin_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = fg_stockin_dtl.sku
update fg_trx_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = fg_trx_dtl.sku
update fg_trx_constr set sku = t.new_sku from #temp_sku t where t.org_sku = fg_trx_constr.sku
update fg_stkform_item set sku = t.new_sku from #temp_sku t where t.org_sku = fg_stkform_item.sku
update fg_stk_item set sku = t.new_sku from #temp_sku t where t.org_sku = fg_stk_item.sku
update fg_stk_constr set sku = t.new_sku from #temp_sku t where t.org_sku = fg_stk_constr.sku
update fg_ship_constr set sku = t.new_sku from #temp_sku t where t.org_sku = fg_ship_constr.sku
update fg_return_constr set sku = t.new_sku from #temp_sku t where t.org_sku = fg_return_constr.sku

update pos_stockin_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_stockin_dtl.sku
update pos_stockin_constr set sku = t.new_sku from #temp_sku t where t.org_sku = pos_stockin_constr.sku

update pos_trx_apply_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_trx_apply_dtl.sku

update pos_trx_out_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_trx_out_dtl.sku
update pos_trx_out_constr set sku = t.new_sku from #temp_sku t where t.org_sku = pos_trx_out_constr.sku

update pos_trx_in_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_trx_in_dtl.sku
update pos_trx_in_constr set sku = t.new_sku from #temp_sku t where t.org_sku = pos_trx_in_constr.sku

update pos_sales_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_sales_dtl.sku

update pos_return_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_return_dtl.sku
update pos_return_constr set sku = t.new_sku from #temp_sku t where t.org_sku = pos_return_constr.sku

update pos_stk_item set sku = t.new_sku from #temp_sku t where t.org_sku = pos_stk_item.sku
update pos_stkform_dtl set sku = t.new_sku from #temp_sku t where t.org_sku = pos_stkform_dtl.sku
update pos_stk_constr set sku = t.new_sku from #temp_sku t where t.org_sku = pos_stk_constr.sku

--
select org_sku, new_sku into #temp_sku2 from #temp_sku
insert into #temp_sku2 select new_sku, new_sku from #temp_sku

--update pos_bin_stock
select t.new_sku as sku, b.shop_code, b.bin_loca, sum(b.qty) as qty, max(b.upd_id) as upd_id
 into #temp_bs
 from #temp_sku2 t, pos_bin_stock b where t.org_sku = b.sku group by t.new_sku, b.shop_code, b.bin_loca

delete from pos_bin_stock where exists (select * from #temp_sku2 t where t.org_sku = pos_bin_stock.sku)

insert into pos_bin_stock (shop_code, bin_loca, sku, qty, upd_id)
 select shop_code, bin_loca, sku, qty, upd_id from #temp_bs

--update pos_st_ctrl
select t.new_sku as sku, b.dc_pick_no, b.org_in_note, b.org_in_seq, b.shop_code,
       sum(b.qty) as qty, max(b.org_in_date) as org_in_date, max(b.last_upd_date) as last_upd_date, max(b.upd_id) as upd_id
 into #temp_bc
 from #temp_sku2 t, pos_st_ctrl b where t.org_sku = b.sku group by t.new_sku, b.dc_pick_no, b.org_in_note, b.org_in_seq, b.shop_code

delete from pos_st_ctrl where exists (select * from #temp_sku2 t where t.org_sku = pos_st_ctrl.sku)

insert into pos_st_ctrl (sku, dc_pick_no, org_in_note, org_in_seq, shop_code, qty, org_in_date, last_upd_date, upd_id)
 select sku, dc_pick_no, org_in_note, org_in_seq, shop_code, qty, org_in_date, last_upd_date, upd_id from #temp_bc


--update pos_st_daily_bal
select t.new_sku as sku, b.shop_code, b.trx_date, sum(b.stock_in_qty) as stock_in_qty, sum(b.return_qty) as return_qty, sum(b.sales_qty) as sales_qty,
 sum(b.trx_in_qty) as trx_in_qty, sum(b.trx_out_qty) as trx_out_qty, sum(b.adj_qty) as adj_qty, sum(b.end_wh_qty) as end_wh_qty, sum(b.end_dp_qty) as end_dp_qty,
 sum(b.stock_in_cost) as stock_in_cost, sum(b.return_cost) as return_cost, sum(b.sales_cost) as sales_cost, sum(b.trx_in_cost) as trx_in_cost,
 sum(b.trx_out_cost) as trx_out_cost, sum(b.adj_cost) as adj_cost, sum(b.end_cost) as end_cost, max(b.upd_id) as upd_id
 into #temp_sb
 from #temp_sku2 t, pos_st_daily_bal b where t.org_sku = b.sku group by t.new_sku, b.shop_code, b.trx_date

delete from pos_st_daily_bal where exists (select * from #temp_sku2 t where t.org_sku = pos_st_daily_bal.sku)

insert into pos_st_daily_bal (shop_code, trx_date, sku, stock_in_qty, return_qty, sales_qty, trx_in_qty, trx_out_qty, adj_qty,
     end_wh_qty, end_dp_qty, stock_in_cost, return_cost, sales_cost, trx_in_cost, trx_out_cost, adj_cost, end_cost, upd_id)
 select shop_code, trx_date, sku, stock_in_qty, return_qty, sales_qty, trx_in_qty, trx_out_qty, adj_qty,
     end_wh_qty, end_dp_qty, stock_in_cost, return_cost, sales_cost, trx_in_cost, trx_out_cost, adj_cost, end_cost, upd_id from #temp_sb
 
alter table ms_style enable trigger all
alter table pos_sku enable trigger all
alter table ms_sku enable trigger all
alter table dc_order_sku enable trigger all
alter table dc_pick_constr enable trigger all
alter table dc_alc_ava_qty enable trigger all
alter table dc_alc_dtl enable trigger all
alter table dc_pick_dtl enable trigger all
alter table fg_st_ctrl enable trigger all
alter table fg_stockin_dtl enable trigger all
alter table fg_trx_dtl enable trigger all
alter table fg_trx_constr enable trigger all
alter table fg_stkform_item enable trigger all
alter table fg_stk_item enable trigger all
alter table fg_stk_constr enable trigger all
alter table fg_ship_constr enable trigger all
alter table fg_return_constr enable trigger all
alter table pos_stockin_dtl enable trigger all
alter table pos_stockin_constr enable trigger all
alter table pos_trx_apply_dtl enable trigger all
alter table pos_trx_out_dtl enable trigger all
alter table pos_trx_out_constr enable trigger all
alter table pos_trx_in_dtl enable trigger all
alter table pos_trx_in_constr enable trigger all
alter table pos_sales_dtl enable trigger all
alter table pos_return_dtl enable trigger all
alter table pos_return_constr enable trigger all
alter table pos_stk_item enable trigger all
alter table pos_stkform_dtl enable trigger all
alter table pos_stk_constr enable trigger all
alter table pos_bin_stock enable trigger all
alter table pos_st_ctrl enable trigger all
alter table pos_st_daily_bal enable trigger all

drop table #temp_sku
commit tran
--rollback tran
